QWI with confidence bands in Excel

Lars Vilhuber
2015-06-16

In this short presentation,

  • we will combine the new rates with the new variability measures to create a graph with error bands in Excel

You will need access to

LEHD Data Page

Screenshot

National QWI Beta page

Screenshot

To download data, scroll to the bottom

Screenshot

Direct download

Downloaded data is gzipped

Screenshot

Unzipping data

Use any number of ZIP-compatible products to unzip (here: 7zip). Right-click, extract. Screenshot

Loading into Excel

Simply double-click on both files Screenshot

Combining sheets

For simplicity, we move the sheets into the same workbook. Screenshot

A single statistics

We focus on HirAEndR (and deleted most other columns after it) Screenshot

Identify standard error on other tab

Screenshot

Creating bounds

On first tab, create columns H_low and H_high Screenshot

Formulae

Strict

\[ X_{low} = X_t - t(0.95,df\_X_t) st\_X_t \]

i.e.

h_low = HirAEndR –

t(df_HirAEndR,0.95)*st_HirAEndR

Approximate

\[ X_{low} = X_t - 1.645 st\_X_t \]

i.e.

h_low = HirAEndR – 1.645*st_HirAEndR

Fill in entire column

Screenshot

Filter to the desired characteristics

Screenshot

Filtered series

Screenshot

Create date column

as *=Date(year,(quarter-1)*3+1,1)*

Screenshot

Select columns for graphing

You want the yyq, HirAEndR and the high/low columns Screenshot

Choose area or line graph

We'll be changing it shortly anyway… Screenshot

Modify the Chart types

Screenshot

Creating bounds

Select “Line” for main series, “Area” for bounds Screenshot (not “Stacked Area”!)

Format graph

  • Delete legend
  • H_high should come before H_low in order
    • color light blue
  • H_low last in order
    • Color white (not transparent!)
  • Adjust size

Voilà!

Screenshot

License

by-nc 4.0

Programs to read in National QWI files and variability measures by Lars Vilhuber is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License